-- creating the event session and adding the event
IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name = N'TempDbContention')
    DROP EVENT SESSION TempdbContention ON SERVER;
GO

CREATE EVENT SESSION TempDbContention ON SERVER 
ADD EVENT sqlserver.latch_suspend_end
(
    WHERE (database_id = 2 AND 
           duration > 0 AND 
           (mode = 2 OR      --PAGELATCH_UP or PAGELATCH_SH (and possibly PAGELATCH_EX)
               mode = 3) AND 
           (page_id < 4 OR   --Initial allocation bitmap pages
               package0.divides_by_uint64(page_id, 8088) OR    --PFS pages
               package0.divides_by_uint64(page_id, 511232) OR  --GAM Pages
               page_id = 511233 OR  --2nd SGAM page 4GB-8GB
               page_id = 1022465 OR --3rd SGAM page 8GB-12GB 
               page_id = 1533697 OR --4th SGAM page 12GB-16GB
               page_id = 2044929 OR --5th SGAM page 16GB-20GB
               page_id = 2556161 OR --6th SGAM page 20GB-24GB
               page_id = 3067393 OR --7th SGAM page 24GB-28GB
               page_id = 3578625)   --8th SGAM page 28GB-32GB
           )
       )
ADD TARGET package0.histogram(
   SET FILTERING_EVENT_NAME = N'sqlserver.latch_suspend_end',
       SOURCE = N'page_id',
       SOURCE_TYPE = 0),
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\TempDbContention.xel',
	    MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);

-- start the event session
ALTER EVENT SESSION TempDbContention ON SERVER STATE = START;
GO

-- monitor the workload

-- querying the histogram target
SELECT
   n.value('(value)[1]', 'bigint') AS page_id,
   n.value('(@count)[1]', 'bigint') AS wait_count
FROM
	(SELECT
		CAST(target_data AS XML) target_data
     FROM sys.dm_xe_sessions AS s 
     INNER JOIN sys.dm_xe_session_targets AS t
       ON s.address = t.event_session_address
     WHERE s.name = N'TempDbContention'
     AND t.target_name = N'histogram'
	) AS tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') AS q(n);

-- querying the event_file target to aggregate wait duration 
SELECT 
    CAST(SUM(n.value('(data[@name="duration"]/value)[1]', 'int'))/1000.0 AS decimal(12,2)) AS duration_ms
FROM 
(SELECT
    CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	-- SQL 2008 (R2)
	-- (N'C:\Temp\TempDbContention*xel', N'C:\Temp\TempDbContention*xem', NULL, NULL)
	-- SQL 2012 no metadata file required
	(N'C:\Temp\TempDbContention*xel', NULL, NULL, NULL)
) AS tab
CROSS APPLY event_data.nodes('//event') AS q(n);

-- stop the event session
ALTER EVENT SESSION TempDbContention ON SERVER STATE = STOP;
GO
 
-- cleanup
DROP EVENT SESSION TempDbContention ON SERVER;
GO